/**
 * Copyright (C), 2020, JieYi Software System Co., Ltd.
 * All rights reserved.
 * FileName:       MdmsDatabaseService.java
 *
 * @Description:
 * @author: feiwe
 * Modification History:
 * Date         Author        Version        Discription
 * -------------------------------------------------------
 * 2020/11/13      feiwe                         add
 */


package com.fengwei.service;

import cn.hutool.core.util.CharsetUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.symmetric.SymmetricAlgorithm;
import cn.hutool.crypto.symmetric.SymmetricCrypto;
import com.alibaba.druid.pool.DruidDataSource;
import com.fengwei.bean.DataSourcePoolManager;
import com.fengwei.config.CommonProperty;
import com.fengwei.dao.MdmsDatabaseMapper;
import com.fengwei.executor.AbstractDbExecutor;
import com.fengwei.form.MdmsDatabase;
import com.fengwei.requestdata.MdmsDatabaseAddData;
import com.fengwei.requestdata.MdmsDatabaseDelData;
import com.fengwei.requestdata.MdmsDatabaseEditData;
import com.fengwei.requestdata.MdmsDatabasePagingListData;
import com.fengwei.requestdata.MdmsDatabaseQueryAllTablesData;
import com.fengwei.requestdata.MdmsDatabaseQueryMyDatabasesData;
import com.fengwei.requestdata.MdmsDatabaseTestConnectionData;
import com.fengwei.util.ExecutorConsts;
import com.fengwei.util.GsonUtil;
import com.fengwei.util.HexStringUtil;
import com.fengwei.util.PltResult;
import com.fengwei.util.SnowflakeIdWorker;
import com.fengwei.util.SpringContextUtil;
import com.fengwei.util.VerifyException;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class MdmsDatabaseService {
    private final static Logger logger = LoggerFactory.getLogger(MdmsDatabaseService.class);

    @Autowired
    private MdmsDatabaseMapper mdmsDatabaseMapper;

    @Autowired
    private SnowflakeIdWorker snowflakeIdWorker;

    @Autowired
    private DataSourcePoolManager dataSourcePoolManager;

    @Autowired
    private CommonProperty commonProperty;

    public Map<String, Object> pagingList(MdmsDatabasePagingListData mdmsDatabasePagingListData) {
        Map mapPagingList = new HashMap();
        PageHelper.startPage(mdmsDatabasePagingListData.getPage(), mdmsDatabasePagingListData.getLimit());
        //String orderBy = SortConsts.getSortStr(mdmsDatabasePagingListData.getOrderby(), SortConsts.USER_ASC_DESC);
        Page<MdmsDatabase> list = mdmsDatabaseMapper.selectByPage(mapPagingList);
        Map<String, Object> mapReturn = new HashMap();
        mapReturn.put("total", list.getTotal());
        mapReturn.put("limit", mdmsDatabasePagingListData.getLimit());
        mapReturn.put("page", mdmsDatabasePagingListData.getPage());
        mapReturn.put("list", list.getResult());
        return mapReturn;
    }

    public Map<String, Object> add(MdmsDatabaseAddData mdmsDatabaseAddData) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByUserCode = new HashMap();

        Map mapInsert = new HashMap();
        mapInsert.put("id", snowflakeIdWorker.nextId());
        mapInsert.put("databaseName", mdmsDatabaseAddData.getDatabaseName());
        mapInsert.put("databaseType", mdmsDatabaseAddData.getDatabaseType());
        mapInsert.put("databaseDbname", mdmsDatabaseAddData.getDatabaseDbname());
        mapInsert.put("databaseHost", mdmsDatabaseAddData.getDatabaseHost());
        mapInsert.put("databasePort", mdmsDatabaseAddData.getDatabasePort());
        mapInsert.put("databaseUser", mdmsDatabaseAddData.getDatabaseUser());
        SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.AES, HexStringUtil.hexStringToBytes(commonProperty.getDatabaseAesKey()));
        String databasePasswordHex = aes.encryptHex(mdmsDatabaseAddData.getDatabasePasswordShow());
        mapInsert.put("databasePassword", databasePasswordHex);
        mdmsDatabaseMapper.insert(mapInsert);

        return returnData;
    }

    public Map<String, Object> edit(MdmsDatabaseEditData mdmsDatabaseEditData) {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByPrimaryKey = new HashMap();
        mapSelectByPrimaryKey.put("id", mdmsDatabaseEditData.getId());
        MdmsDatabase mdmsDatabase = mdmsDatabaseMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsDatabase) {
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF300);
        } else {
            MdmsDatabase mdmsDatabaseUpdate = new MdmsDatabase();
            mdmsDatabaseUpdate.setId(mdmsDatabase.getId());
            mdmsDatabaseUpdate.setDatabaseName(mdmsDatabaseEditData.getDatabaseName());
            mdmsDatabaseUpdate.setDatabaseType(mdmsDatabaseEditData.getDatabaseType());
            mdmsDatabaseUpdate.setDatabaseDbname(mdmsDatabaseEditData.getDatabaseDbname());
            mdmsDatabaseUpdate.setDatabaseHost(mdmsDatabaseEditData.getDatabaseHost());
            mdmsDatabaseUpdate.setDatabasePort(mdmsDatabaseEditData.getDatabasePort());
            mdmsDatabaseUpdate.setDatabaseUser(mdmsDatabaseEditData.getDatabaseUser());
            if (!StrUtil.hasEmpty(mdmsDatabaseEditData.getDatabasePasswordShow())) {
                SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.AES, HexStringUtil.hexStringToBytes(commonProperty.getDatabaseAesKey()));
                String databasePasswordHex = aes.encryptHex(mdmsDatabaseEditData.getDatabasePasswordShow());
                mdmsDatabaseUpdate.setDatabasePassword(databasePasswordHex);
            }
            mdmsDatabaseMapper.update(mdmsDatabaseUpdate);

            //如果此时有连接池存在，则先删除
            DruidDataSource druidDataSource = dataSourcePoolManager.getDataSourcePoolManagerMap().get(mdmsDatabase.getId() + "");
            if (null != druidDataSource) {
                druidDataSource.close();
            }
        }

        return returnData;
    }

    public Map<String, Object> del(MdmsDatabaseDelData mdmsDatabaseDelData) {
        Map<String, Object> returnData = new HashMap<>();
        Map mapSelectByPrimaryKey = new HashMap();
        mapSelectByPrimaryKey.put("id", mdmsDatabaseDelData.getId());
        MdmsDatabase mdmsDatabase = mdmsDatabaseMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsDatabase) {
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF300);
        } else {
            MdmsDatabase mdmsDatabaseDel = new MdmsDatabase();
            mdmsDatabaseDel.setId(mdmsDatabase.getId());
            mdmsDatabaseMapper.delete(mdmsDatabaseDel);

            //如果此时有连接池存在，则先删除
            DruidDataSource druidDataSource = dataSourcePoolManager.getDataSourcePoolManagerMap().get(mdmsDatabase.getId() + "");
            if (null != druidDataSource) {
                druidDataSource.close();
            }
        }

        return returnData;
    }

    public Map<String, Object> testConnetion(MdmsDatabaseTestConnectionData mdmsDatabaseTestConnectionData) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabaseTestConnectionData.getDatabaseType() + "Executor");
        String url = abstractDbExecutor.genUrl(mdmsDatabaseTestConnectionData.getDatabaseType(), mdmsDatabaseTestConnectionData.getDatabaseHost(), mdmsDatabaseTestConnectionData.getDatabasePort(),
                mdmsDatabaseTestConnectionData.getDatabaseDbname());
        try {
            String databasePassword = mdmsDatabaseTestConnectionData.getDatabasePasswordShow();
            if (StrUtil.hasEmpty(databasePassword)) {
                databasePassword = mdmsDatabaseTestConnectionData.getDatabasePassword();
                if (StrUtil.hasEmpty(databasePassword)) {
                    throw new VerifyException(PltResult.RESULT_CF308);
                } else {
                    SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.AES, HexStringUtil.hexStringToBytes(commonProperty.getDatabaseAesKey()));
                    databasePassword = aes.decryptStr(databasePassword, CharsetUtil.CHARSET_UTF_8);
                }
            }

            logger.debug("databasePassword:" + databasePassword);

            abstractDbExecutor.testConnection(mdmsDatabaseTestConnectionData.getDatabaseType(), url, mdmsDatabaseTestConnectionData.getDatabaseUser(), databasePassword);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            if (e instanceof VerifyException) {
                throw e;
            } else {
                VerifyException verifyException = new VerifyException(PltResult.RESULT_CF309);
                verifyException.setErrorDesc(PltResult.RESULT_CF309.getpName() + ":" + e.getMessage());
                // 数据库不存在
                throw verifyException;
            }

        }

        return returnData;
    }

    public Map<String, Object> queryMyDatabases(MdmsDatabaseQueryMyDatabasesData mdmsDatabaseQueryMyDatabasesData) {
        Map<String, Object> returnData = new HashMap<>();
        List<MdmsDatabase> list = mdmsDatabaseMapper.selectAll(null);
        returnData.put("list", list);
        return returnData;
    }

    public Map<String, Object> queryAllTables(MdmsDatabaseQueryAllTablesData mdmsDatabaseQueryAllTablesData, HttpServletRequest request) throws Exception {
        Map<String, Object> returnData = new HashMap<>();
        HttpSession session = request.getSession();
        Map mapSelectByPrimaryKey = new HashMap();
        mapSelectByPrimaryKey.put("id", mdmsDatabaseQueryAllTablesData.getId());
        MdmsDatabase mdmsDatabase = mdmsDatabaseMapper.selectByPrimaryKey(mapSelectByPrimaryKey);
        if (null == mdmsDatabase) {
            // 数据库不存在
            throw new VerifyException(PltResult.RESULT_CF300);
        } else {
            AbstractDbExecutor abstractDbExecutor = (AbstractDbExecutor) SpringContextUtil.getBean(mdmsDatabase.getDatabaseType() + "Executor");
            String url = abstractDbExecutor.genUrl(mdmsDatabase.getDatabaseType(), mdmsDatabase.getDatabaseHost(), mdmsDatabase.getDatabasePort(),
                    mdmsDatabase.getDatabaseDbname());
            Connection connection = null;
            try {
                connection = abstractDbExecutor.getConnection(mdmsDatabase.getId() + "", url, mdmsDatabase.getDatabaseUser(), mdmsDatabase.getDatabasePassword());

                String paramName = "";
                if (ExecutorConsts.DBTYPE_MYSQL.equals(mdmsDatabase.getDatabaseType())) {
                    //mysql靠库名来获取所有表
                    paramName = mdmsDatabase.getDatabaseDbname();
                } else if (ExecutorConsts.DBTYPE_ORACLE.equals(mdmsDatabase.getDatabaseType())) {
                    //oracle靠用户名来获取所有表
                    paramName = mdmsDatabase.getDatabaseUser();
                }
                List<String> tableList = abstractDbExecutor.getAllTables(connection, paramName);
                logger.debug(GsonUtil.toJson(tableList, false));
                if (tableList.size() == 0) {
                    throw new VerifyException(PltResult.RESULT_CF302);
                } else {
                    List dbInfo = new ArrayList();
                    Map mapTable = new HashMap();
                    mapTable.put("id", mdmsDatabase.getDatabaseDbname());
                    mapTable.put("name", "表");
                    mapTable.put("iconCls", "icon-diy-folder1");
                    List<Map<String, String>> tableChild = new ArrayList();
                    for (String table : tableList) {
                        Map<String, String> m = new HashMap();
                        m.put("id", table);
                        m.put("name", table);
                        m.put("iconCls", "icon-diy-table1");
                        tableChild.add(m);
                    }
                    mapTable.put("children", tableChild);
                    dbInfo.add(mapTable);
                    //TODO:先做table，其它的以后再说

                    returnData.put("dbInfo", dbInfo);
                }

                //当前使用的数据库放在session当中
                session.setAttribute("currentDatabaseId", mdmsDatabase);
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                // 数据库不存在
                throw new VerifyException(PltResult.RESULT_CF301);
            } finally {
                abstractDbExecutor.closeConnection(connection);
            }
        }
        return returnData;
    }


}
